package com.mky.mkcompany.dao;

import com.mky.mkcompany.model.PracticQuest;
import com.mky.mkcompany.model.SubjectChapter;
import com.mky.mkcompany.model.UserDoPracticQuest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Transactional
public interface UserDoPracticQuestDao extends JpaRepository<UserDoPracticQuest, String> {

    @Query("select count(*) from UserDoPracticQuest up,PracticQuest p where p.examType=:examType and p.id=up.questId" +
            " and up.userId=:userId and up.answerResult=:answerResult")
    long findCountQuestByAnswerResult(@Param("examType") String examType,
                                      @Param("userId") String userId,
                                      @Param("answerResult") Integer answerResult);

    @Query("select p from UserDoPracticQuest up,PracticQuest p where p.examType=:examType and " +
            "p.id=up.questId  and up.userId=:userId  and up.answerResult=:answerResult")
    Page<PracticQuest> findPracticQuestByAnswerResult(@Param("examType") String examType,
                                                      @Param("userId") String userId,
                                                      @Param("answerResult") Integer answerResult,
                                                      Pageable pageable);

    @Query("select up.questId from UserDoPracticQuest up,PracticQuest p where p.examType=:examType and " +
            "p.id =up.questId  and up.userId=:userId and up.answerResult!=0")
    List<String> findDoPracticQuestByUserId(@Param("examType") String examType,
                                                      @Param("userId") String userId);

    @Query("select count(*) from UserDoPracticQuest up,PracticQuest p,SubjectChapter sc" +
            " where p.examType=:examType and p.id=up.questId and sc.subjectId=:subjectId and p.chapterId=sc.id " +
            " and up.userId=:userId and up.answerResult=:answerResult")
    long findCountQuestByAnswerResultSubId(@Param("examType") String examType,
                                           @Param("userId") String userId,
                                           @Param("subjectId") String subjectId,
                                           @Param("answerResult") Integer answerResult);

    @Query("select p from UserDoPracticQuest up,PracticQuest p,SubjectChapter sc" +
            " where p.examType=:examType and p.id=up.questId and sc.subjectId=:subjectId and p.chapterId=sc.id " +
            " and up.userId=:userId and up.answerResult=:answerResult")
    Page<PracticQuest>  findPracticQuestByAnswerResultSubId(@Param("examType") String examType,
                                                            @Param("userId") String userId,
                                           @Param("subjectId") String subjectId,
                                           @Param("answerResult") Integer answerResult,
                                                            Pageable pageable);

    @Query("select up.questId from UserDoPracticQuest up,PracticQuest p,SubjectChapter sc" +
            " where p.examType=:examType and p.id=up.questId and sc.subjectId=:subjectId and p.chapterId=sc.id " +
            " and up.userId=:userId and up.answerResult!=0")
    List<String>  findDoPracticQuestBySubId(@Param("examType") String examType,
                                                            @Param("userId") String userId,
                                                            @Param("subjectId") String subjectId);

    @Query("select count(*) from UserDoPracticQuest up,PracticQuest p" +
            " where p.examType=:examType and p.id=up.questId and p.chapterId=:chapterId " +
            " and up.userId=:userId  and up.answerResult=:answerResult")
    long findCountQuestByAnswerResultChapId(@Param("examType") String examType,
                                            @Param("userId") String userId,
                                           @Param("chapterId") String chapterId,
                                           @Param("answerResult") Integer answerResult);

    @Query("select p from UserDoPracticQuest up,PracticQuest p" +
            " where p.examType=:examType and p.id=up.questId and p.chapterId=:chapterId " +
            " and up.userId=:userId and up.answerResult=:answerResult")
    Page<PracticQuest>  findPracticQuestByAnswerResultChapId(@Param("examType") String examType,
                                                             @Param("userId") String userId,
                                                             @Param("chapterId") String chapterId,
                                                             @Param("answerResult") Integer answerResult,
                                                             Pageable pageable);

    @Query("select up.questId from UserDoPracticQuest up,PracticQuest p" +
            " where p.examType=:examType and p.id=up.questId and p.chapterId=:chapterId " +
            " and up.userId=:userId and up.answerResult!=0")
    List<String>  findPracticQuestByChapId(@Param("examType") String examType,
                                                             @Param("userId") String userId,
                                                             @Param("chapterId") String chapterId);

    UserDoPracticQuest findByQuestIdAndUserId(String questId,String userId);

    @Query("select up from UserDoPracticQuest up,PracticQuest p" +
            " where p.examType=:examType and p.id=up.questId " +
            " and up.userId=:userId")
    List<UserDoPracticQuest> findObjsByExamType(@Param("examType") String examType,
                                                @Param("userId") String userId);

    @Transactional
    @Modifying
    @Query("delete from UserDoPracticQuest where questId = ?1 ")
    void deleteByQuestId(String questId);

    @Transactional
    @Modifying
    @Query("delete from UserDoPracticQuest where questId in (:questIds)")
    void deleteByQuestId(@Param("questIds")List<String> questIds);

    @Transactional
    @Modifying
    @Query("delete from UserDoPracticQuest where userId = ?1 ")
    void deleteByUserId(String userId);
}